// 项目管理接口
import db;
import response;

// 获取项目列表
var getProjects = () => {
    var sql = """
        SELECT p.*, u.nickname as creator_name,
               (SELECT COUNT(*) FROM tasks t WHERE t.project_id = p.id) as task_count,
               (SELECT COUNT(*) FROM project_members pm WHERE pm.project_id = p.id) as member_count
        FROM projects p
        LEFT JOIN users u ON p.created_by = u.id
        ORDER BY p.created_at DESC
    """;
    var projects = db.select(sql);
    return response.json(projects);
};

// 获取项目详情
var getProjectDetail = (id) => {
    if (!id) {
        return response.json({error: "项目ID不能为空"});
    }
    
    // 项目基本信息
    var projectSql = """
        SELECT p.*, u.nickname as creator_name
        FROM projects p
        LEFT JOIN users u ON p.created_by = u.id
        WHERE p.id = #{id}
    """;
    var project = db.selectOne(projectSql, {id: id});
    
    if (!project) {
        return response.json({error: "项目不存在"});
    }
    
    // 项目成员
    var membersSql = """
        SELECT u.id, u.nickname as name, u.avatar, pm.role
        FROM project_members pm
        LEFT JOIN users u ON pm.user_id = u.id
        WHERE pm.project_id = #{id}
    """;
    var members = db.select(membersSql, {id: id});
    
    // 项目任务统计
    var taskStatsSql = """
        SELECT 
            COUNT(*) as total,
            COUNT(CASE WHEN status = '已完成' THEN 1 END) as completed,
            COUNT(CASE WHEN status != '已完成' THEN 1 END) as in_progress
        FROM tasks
        WHERE project_id = #{id}
    """;
    var taskStats = db.selectOne(taskStatsSql, {id: id});
    
    // 工作流状态
    var workflowSql = """
        SELECT * FROM workflow_statuses
        WHERE project_id = #{id}
        ORDER BY order_num
    """;
    var workflows = db.select(workflowSql, {id: id});
    
    // 最新5个任务
    var tasksSql = """
        SELECT t.*, u.nickname as assignee_name
        FROM tasks t
        LEFT JOIN users u ON t.assignee_id = u.id
        WHERE t.project_id = #{id}
        ORDER BY t.created_at DESC
        LIMIT 5
    """;
    var tasks = db.select(tasksSql, {id: id});
    
    return response.json({
        project: project,
        members: members,
        taskStats: taskStats,
        workflows: workflows,
        tasks: tasks
    });
};

// 创建项目
var createProject = (name, description, createdBy) => {
    if (!name || !createdBy) {
        return response.json({error: "项目名称和创建者不能为空"});
    }
    
    // 开启事务
    var tx = db.transaction();
    try {
        // 创建项目
        var projectId = db.insert("""
            INSERT INTO projects (name, description, created_by)
            VALUES (#{name}, #{description}, #{createdBy})
        """, {
            name: name,
            description: description,
            createdBy: createdBy
        });
        
        // 创建默认工作流状态
        var defaultStatuses = [
            {name: "待处理", color: "#FF9500", order: 1},
            {name: "处理中", color: "#007AFF", order: 2},
            {name: "待测试", color: "#AF52DE", order: 3},
            {name: "已完成", color: "#34C759", order: 4}
        ];
        
        for (var status in defaultStatuses) {
            db.insert("""
                INSERT INTO workflow_statuses (project_id, name, color, order_num)
                VALUES (#{projectId}, #{name}, #{color}, #{order})
            """, {
                projectId: projectId,
                name: status.name,
                color: status.color,
                order: status.order
            });
        }
        
        // 将创建者添加为项目成员
        db.insert("""
            INSERT INTO project_members (project_id, user_id, role)
            VALUES (#{projectId}, #{createdBy}, '项目经理')
        """, {
            projectId: projectId,
            createdBy: createdBy
        });
        
        tx.commit();
        return response.json({id: projectId, message: "项目创建成功"});
    } catch (e) {
        tx.rollback();
        return response.json({error: "项目创建失败: " + e.message});
    }
};

// 更新项目
var updateProject = (id, name, description, status) => {
    if (!id || !name) {
        return response.json({error: "项目ID和名称不能为空"});
    }
    
    var result = db.update("""
        UPDATE projects
        SET name = #{name}, description = #{description}, status = #{status}
        WHERE id = #{id}
    """, {
        id: id,
        name: name,
        description: description,
        status: status
    });
    
    if (result > 0) {
        return response.json({message: "项目更新成功"});
    } else {
        return response.json({error: "项目更新失败"});
    }
};

// 删除项目
var deleteProject = (id) => {
    if (!id) {
        return response.json({error: "项目ID不能为空"});
    }
    
    var result = db.update("""
        DELETE FROM projects WHERE id = #{id}
    """, {id: id});
    
    if (result > 0) {
        return response.json({message: "项目删除成功"});
    } else {
        return response.json({error: "项目删除失败"});
    }
};

// 任务管理接口

// 获取任务列表
var getTasks = (projectId, status, assigneeId) => {
    var sql = """
        SELECT t.*, p.name as project_name, u.nickname as assignee_name, c.nickname as creator_name
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id
        LEFT JOIN users u ON t.assignee_id = u.id
        LEFT JOIN users c ON t.created_by = c.id
        WHERE 1=1
        ?{projectId, AND t.project_id = #{projectId}}
        ?{status, AND t.status = #{status}}
        ?{assigneeId, AND t.assignee_id = #{assigneeId}}
        ORDER BY t.created_at DESC
    """;
    
    var tasks = db.select(sql, {
        projectId: projectId,
        status: status,
        assigneeId: assigneeId
    });
    
    return response.json(tasks);
};

// 获取任务详情
var getTaskDetail = (id) => {
    if (!id) {
        return response.json({error: "任务ID不能为空"});
    }
    
    // 任务基本信息
    var taskSql = """
        SELECT t.*, p.name as project_name, u.nickname as assignee_name, c.nickname as creator_name
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id
        LEFT JOIN users u ON t.assignee_id = u.id
        LEFT JOIN users c ON t.created_by = c.id
        WHERE t.id = #{id}
    """;
    var task = db.selectOne(taskSql, {id: id});
    
    if (!task) {
        return response.json({error: "任务不存在"});
    }
    
    // 任务附件
    var attachmentsSql = """
        SELECT * FROM task_attachments
        WHERE task_id = #{id}
        ORDER BY uploaded_at DESC
    """;
    var attachments = db.select(attachmentsSql, {id: id});
    
    // 任务日志
    var logsSql = """
        SELECT tl.*, u.nickname as user_name
        FROM task_logs tl
        LEFT JOIN users u ON tl.user_id = u.id
        WHERE tl.task_id = #{id}
        ORDER BY tl.created_at DESC
    """;
    var logs = db.select(logsSql, {id: id});
    
    return response.json({
        task: task,
        attachments: attachments,
        logs: logs
    });
};

// 创建任务
var createTask = (projectId, title, description, priority, status, assigneeId, createdById) => {
    if (!projectId || !title || !createdById) {
        return response.json({error: "项目ID、任务标题和创建者不能为空"});
    }
    
    var taskId = db.insert("""
        INSERT INTO tasks (project_id, title, description, priority, status, assignee_id, created_by)
        VALUES (#{projectId}, #{title}, #{description}, #{priority}, #{status}, #{assigneeId}, #{createdById})
    """, {
        projectId: projectId,
        title: title,
        description: description,
        priority: priority || "中",
        status: status || "待处理",
        assigneeId: assigneeId,
        createdById: createdById
    });
    
    // 添加任务创建日志
    db.insert("""
        INSERT INTO task_logs (task_id, user_id, action, description)
        VALUES (#{taskId}, #{userId}, '创建任务', '创建了任务: #{title}')
    """, {
        taskId: taskId,
        userId: createdById,
        title: title
    });
    
    return response.json({id: taskId, message: "任务创建成功"});
};

// 更新任务
var updateTask = (id, title, description, priority, status, assigneeId) => {
    if (!id || !title) {
        return response.json({error: "任务ID和标题不能为空"});
    }
    
    var result = db.update("""
        UPDATE tasks
        SET title = #{title}, description = #{description}, priority = #{priority}, 
            status = #{status}, assignee_id = #{assigneeId}
        WHERE id = #{id}
    """, {
        id: id,
        title: title,
        description: description,
        priority: priority,
        status: status,
        assigneeId: assigneeId
    });
    
    if (result > 0) {
        return response.json({message: "任务更新成功"});
    } else {
        return response.json({error: "任务更新失败"});
    }
};

// 删除任务
var deleteTask = (id) => {
    if (!id) {
        return response.json({error: "任务ID不能为空"});
    }
    
    var result = db.update("""
        DELETE FROM tasks WHERE id = #{id}
    """, {id: id});
    
    if (result > 0) {
        return response.json({message: "任务删除成功"});
    } else {
        return response.json({error: "任务删除失败"});
    }
};

// 用户管理接口

// 用户登录
var login = (username, password) => {
    if (!username || !password) {
        return response.json({error: "用户名和密码不能为空"});
    }
    
    var user = db.selectOne("""
        SELECT id, username, nickname, email, avatar
        FROM users
        WHERE username = #{username} AND password = #{password}
    """, {
        username: username,
        password: password
    });
    
    if (user) {
        return response.json({user: user, message: "登录成功"});
    } else {
        return response.json({error: "用户名或密码错误"});
    }
};

// 获取用户信息
var getUserInfo = (id) => {
    if (!id) {
        return response.json({error: "用户ID不能为空"});
    }
    
    var user = db.selectOne("""
        SELECT id, username, nickname, email, avatar, created_at
        FROM users
        WHERE id = #{id}
    """, {id: id});
    
    if (user) {
        return response.json(user);
    } else {
        return response.json({error: "用户不存在"});
    }
};

// 统计接口

// 获取统计概览
var getStatistics = () => {
    // 项目统计
    var projectStats = db.selectOne("""
        SELECT 
            COUNT(*) as total,
            COUNT(CASE WHEN status = '进行中' THEN 1 END) as in_progress,
            COUNT(CASE WHEN status = '已归档' THEN 1 END) as archived
        FROM projects
    """);
    
    // 任务统计
    var taskStats = db.selectOne("""
        SELECT 
            COUNT(*) as total,
            COUNT(CASE WHEN status = '已完成' THEN 1 END) as completed,
            COUNT(CASE WHEN status != '已完成' THEN 1 END) as in_progress
        FROM tasks
    """);
    
    // 用户统计
    var userStats = db.selectOne("""
        SELECT COUNT(*) as total FROM users
    """);
    
    return response.json({
        projects: projectStats,
        tasks: taskStats,
        users: userStats
    });
};

// 获取项目进度统计
var getProjectProgress = () => {
    var sql = """
        SELECT 
            p.name,
            COUNT(t.id) as total_tasks,
            COUNT(CASE WHEN t.status = '已完成' THEN 1 END) as completed_tasks,
            CASE 
                WHEN COUNT(t.id) = 0 THEN 0
                ELSE ROUND(COUNT(CASE WHEN t.status = '已完成' THEN 1 END) * 100.0 / COUNT(t.id), 2)
            END as progress
        FROM projects p
        LEFT JOIN tasks t ON p.id = t.project_id
        GROUP BY p.id, p.name
        ORDER BY progress DESC
    """;
    
    var stats = db.select(sql);
    return response.json(stats);
};